package cn.reinforce.util.poi;

import cn.reinforce.util.exception.ExcelException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFObjectData;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.ObjectData;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFObjectData;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

    public static void createCell(Row row, int column, String content) {
        Cell cell = row.createCell(column);
        RichTextString text = null;
        if (row instanceof XSSFRow) {
            text = new XSSFRichTextString(content);
        } else {
            text = new HSSFRichTextString(content);
        }
        cell.setCellValue(text);
    }

    public static void createCell(Row row, int column, String content, CellStyle style) {
        Cell cell = row.createCell(column);
        RichTextString text = null;
        if (row instanceof XSSFRow) {
            text = new XSSFRichTextString(content);
        } else {
            text = new HSSFRichTextString(content);
        }
        cell.setCellValue(text);
        cell.setCellStyle(style);
    }

    public static void createCell(Row row, int column, Integer content) {
        createCell(row, column, Integer.toString(content));
    }

    public static void createCell(Row row, int column, Integer content, CellStyle style) {
        createCell(row, column, Integer.toString(content), style);
    }

    public static void createCell(Row row, int column, Double content) {
        createCell(row, column, Double.toString(content));
    }

    public static void createCell(Row row, int column, Double content, CellStyle style) {
        createCell(row, column, Double.toString(content), style);
    }

    /**
     * 获取单元格数据内容为字符串类型的数据
     *
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    public static String getStringCellValue(Cell cell) {
        String strCell = "";
        if (cell == null || StringUtils.isEmpty(StringUtils.trim(cell.toString()))) {
            return strCell;
        }
        switch (cell.getCellType()) {
            case STRING:
                strCell = cell.getStringCellValue();
                break;
            case NUMERIC:
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil
                                .getJavaDate(value);
                        strCell = sdf.format(date);
                    } else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        strCell = sdf.format(cell.getDateCellValue());// 日期
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    } finally {
                        sdf = null;
                    }
                } else {
                    DecimalFormat df = new DecimalFormat("0.##");
                    strCell = df.format(cell.getNumericCellValue());
                }
                break;
            case FORMULA:
                strCell = cell.getCellFormula();
                break;
            case BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case BLANK:
                strCell = "";
                break;
            case ERROR:
                strCell = "";
                break;
            default:
                strCell = "UNKNOW VALUE";
                break;
        }

        return StringUtils.trim(strCell);
    }

    public static Map<Integer, List<PictureData>> getPictures(Sheet sheet) throws IOException {
        if (sheet instanceof HSSFSheet) {
            return getPicturesByHssf((HSSFSheet) sheet);
        } else if (sheet instanceof XSSFSheet) {
            return getPicturesByXssf((XSSFSheet) sheet);
        }
        return null;
    }

    public static Map<Integer, List<ObjectData>> getObjects(Sheet sheet) throws IOException {
        if (sheet instanceof HSSFSheet) {
            return getObjectsByHssf((HSSFSheet) sheet);
        } else if (sheet instanceof XSSFSheet) {
            return getObjectsByXssf((XSSFSheet) sheet);
        }
        return null;
    }

    public static Map<Integer, List<PictureData>> getPicturesByHssf(HSSFSheet sheet) throws IOException {
        Map<Integer, List<PictureData>> map = new HashMap<>();
        if (sheet.getDrawingPatriarch() == null) {
            return map;
        }
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                PictureData pData = picture.getPictureData();
                if (map.containsKey(cAnchor.getRow1() + 1)) {
                    map.get(cAnchor.getRow1() + 1).add(pData);
                } else {
                    List<PictureData> pictureData = new ArrayList<>();
                    pictureData.add(pData);
                    map.put(cAnchor.getRow1() + 1, pictureData);
                }
            }
        }
        return map;
    }

    public static Map<Integer, List<ObjectData>> getObjectsByHssf(HSSFSheet sheet) throws IOException {
        Map<Integer, List<ObjectData>> map = new HashMap<>();
        if (sheet.getDrawingPatriarch() == null) {
            return map;
        }
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof ObjectData) {
                HSSFObjectData obj = (HSSFObjectData) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) obj.getAnchor();
                if (map.containsKey(cAnchor.getRow1() + 1)) {
                    map.get(cAnchor.getRow1() + 1).add(obj);
                } else {
                    List<ObjectData> objectData = new ArrayList<>();
                    objectData.add(obj);
                    map.put(cAnchor.getRow1() + 1, objectData);
                }
            }
        }
        return map;
    }

    /**
     * 获取图片和位置 (xlsx)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<Integer, List<PictureData>> getPicturesByXssf(XSSFSheet sheet) throws IOException {
        Map<Integer, List<PictureData>> map = new HashMap<>();
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    if (shape instanceof XSSFPicture) {
                        XSSFPicture picture = (XSSFPicture) shape;
                        XSSFClientAnchor anchor = picture.getPreferredSize();
                        CTMarker marker = anchor.getFrom();

                        if (map.containsKey(marker.getRow() + 1)) {
                            map.get(marker.getRow() + 1).add(picture.getPictureData());
                        } else {
                            List<PictureData> pictureData = new ArrayList<>();
                            pictureData.add(picture.getPictureData());
                            map.put(marker.getRow() + 1, pictureData);
                        }
                    }
                }
            }
        }

        return map;
    }

    public static Map<Integer, List<ObjectData>> getObjectsByXssf(XSSFSheet sheet) throws IOException {
        Map<Integer, List<ObjectData>> map = new HashMap<>();
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    if (shape instanceof XSSFObjectData) {
                        XSSFObjectData obj = (XSSFObjectData) shape;
                        XSSFClientAnchor anchor = (XSSFClientAnchor) obj.getAnchor();
                        CTMarker marker = anchor.getFrom();

                        if (map.containsKey(marker.getRow() + 1)) {
                            map.get(marker.getRow() + 1).add(obj);
                        } else {
                            List<ObjectData> objectData = new ArrayList<>();
                            objectData.add(obj);
                            map.put(marker.getRow() + 1, objectData);
                        }
                    }
                }
            }
        }

        return map;
    }

    /**
     * 复制工作表
     * 此方法主要用于复制2个不同HSSFWorkbook间的工作表
     */
    public static void copySheet(Workbook fromWorkbook, Workbook toWorkbook, int fromSheetIndex, int toSheetIndex) {
        toWorkbook.setSheetName(toSheetIndex, fromWorkbook.getSheetName(fromSheetIndex));
        Sheet fromSheet = fromWorkbook.getSheetAt(fromSheetIndex);
        for (int i = fromSheet.getFirstRowNum(); i <= fromSheet.getLastRowNum(); i++) {
            copyRows(fromWorkbook, toWorkbook, fromSheetIndex, toSheetIndex, i, i, i);
        }
    }

    /**
     * 复制行
     * 此方法主要用于复制2个不同HSSFWorkbook间的行
     */
    public static void copyRows(Workbook fromWorkbook, Workbook toWorkbook, int fromSheetIndex, int toSheetIndex, int startRow, int endRow, int position) {
        Sheet fromSheet = fromWorkbook.getSheetAt(fromSheetIndex);
        Sheet toSheet = toWorkbook.getSheetAt(toSheetIndex);
        int i;
        int j;

        if ((startRow == -1) || (endRow == -1)) {
            return;
        }

        List<CellRangeAddress> oldRanges = new ArrayList<CellRangeAddress>();
        for (i = 0; i < fromSheet.getNumMergedRegions(); i++) {
            oldRanges.add(fromSheet.getMergedRegion(i));
        }

        // 拷贝合并的单元格。原理：复制当前合并单元格后，原位置的格式会移动到新位置，需在原位置生成旧格式
        for (CellRangeAddress oldRange : oldRanges) {
            CellRangeAddress newRange = new CellRangeAddress(oldRange.getFirstRow(), oldRange.getLastRow(),
                    oldRange.getFirstColumn(), oldRange.getLastColumn());

            if (oldRange.getFirstRow() >= startRow && oldRange.getLastRow() <= endRow) {
                int targetRowFrom = oldRange.getFirstRow() - startRow + position;
                int targetRowTo = oldRange.getLastRow() - startRow + position;
                oldRange.setFirstRow(targetRowFrom);
                oldRange.setLastRow(targetRowTo);
                toSheet.addMergedRegion(oldRange);
            }
        }

        // 设置列宽
        for (i = startRow; i <= endRow; i++) {
            Row fromRow = fromSheet.getRow(i);
            if (fromRow != null) {
                for (j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) {
                    toSheet.setColumnWidth(j, fromSheet.getColumnWidth(j));
                    toSheet.setColumnHidden(j, false);
                }
                break;
            }
        }

        // 拷贝行并填充数据
        for (; i <= endRow; i++) {
            Row fromRow = fromSheet.getRow(i);
            if (fromRow == null) {
                continue;
            }
            Row toRow = toSheet.createRow(i - startRow + position);
            toRow.setHeight(fromRow.getHeight());
            if(fromRow.getFirstCellNum() >= 0) {
                for (j = fromRow.getFirstCellNum(); j <= fromRow.getLastCellNum(); j++) {
                    Cell fromCell = fromRow.getCell(j);
                    if (fromCell == null) {
                        continue;
                    }
                    Cell toCell = toRow.createCell(j);
                    CellStyle toStyle = toWorkbook.createCellStyle();
                    copyCellStyle(fromWorkbook, toWorkbook, fromCell.getCellStyle(), toStyle);
                    toCell.setCellStyle(toStyle);
                    CellType cType = fromCell.getCellType();
                    toCell.setCellType(cType);
                    switch (cType) {
                        case BOOLEAN:
                            toCell.setCellValue(fromCell.getBooleanCellValue());
                            // System.out.println("--------TYPE_BOOLEAN:" +
                            // targetCell.getBooleanCellValue());
                            break;
                        case ERROR:
                            toCell.setCellErrorValue(fromCell.getErrorCellValue());
                            // System.out.println("--------TYPE_ERROR:" +
                            // targetCell.getErrorCellValue());
                            break;
                        case FORMULA:
                            toCell.setCellFormula(parseFormula(fromCell.getCellFormula()));
                            // System.out.println("--------TYPE_FORMULA:" +
                            // targetCell.getCellFormula());
                            break;
                        case NUMERIC:
                            toCell.setCellValue(fromCell.getNumericCellValue());
                            // System.out.println("--------TYPE_NUMERIC:" +
                            // targetCell.getNumericCellValue());
                            break;
                        case STRING:
                            toCell.setCellValue(fromCell.getRichStringCellValue());
                            // System.out.println("--------TYPE_STRING:" + i +
                            // targetCell.getRichStringCellValue());
                            break;
                    }
                }
            }
        }
    }

    /**
     * 复制行
     * 如果是同一个HSSFWorkbook中的行请用此方法
     */
    public static void copyRows(Workbook workbook, int fromSheetIndex, int toSheetIndex, int startRow, int endRow, int position) {
        Sheet fromSheet = workbook.getSheetAt(fromSheetIndex);
        Sheet toSheet = workbook.getSheetAt(toSheetIndex);
        int i;
        int j;

        if ((startRow == -1) || (endRow == -1)) {
            return;
        }

        List<CellRangeAddress> oldRanges = new ArrayList<CellRangeAddress>();
        for (i = 0; i < fromSheet.getNumMergedRegions(); i++) {
            oldRanges.add(fromSheet.getMergedRegion(i));
        }

        // 拷贝合并的单元格。原理：复制当前合并单元格后，原位置的格式会移动到新位置，需在原位置生成旧格式
        for (CellRangeAddress oldRange : oldRanges) {
            CellRangeAddress newRange = new CellRangeAddress(oldRange.getFirstRow(), oldRange.getLastRow(),
                    oldRange.getFirstColumn(), oldRange.getLastColumn());

            if (oldRange.getFirstRow() >= startRow && oldRange.getLastRow() <= endRow) {
                int targetRowFrom = oldRange.getFirstRow() - startRow + position;
                int targetRowTo = oldRange.getLastRow() - startRow + position;
                oldRange.setFirstRow(targetRowFrom);
                oldRange.setLastRow(targetRowTo);
                toSheet.addMergedRegion(oldRange);
                fromSheet.addMergedRegion(newRange);
            }
        }

        // 设置列宽
        for (i = startRow; i <= endRow; i++) {
            Row fromRow = fromSheet.getRow(i);
            if (fromRow != null) {
                for (j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) {
                    toSheet.setColumnWidth(j, fromSheet.getColumnWidth(j));
                    toSheet.setColumnHidden(j, false);
                }
                break;
            }
        }

        // 拷贝行并填充数据
        for (; i <= endRow; i++) {
            Row fromRow = fromSheet.getRow(i);
            if (fromRow == null) {
                continue;
            }
            Row toRow = toSheet.createRow(i - startRow + position);
            toRow.setHeight(fromRow.getHeight());
            if(fromRow.getFirstCellNum() >= 0) {
                for (j = fromRow.getFirstCellNum(); j <= fromRow.getLastCellNum(); j++) {
                    Cell fromCell = fromRow.getCell(j);
                    if (fromCell == null) {
                        continue;
                    }
                    Cell toCell = toRow.createCell(j);
                    toCell.setCellStyle(fromCell.getCellStyle());
                    CellType cType = fromCell.getCellType();
                    toCell.setCellType(cType);
                    switch (cType) {
                        case BOOLEAN:
                            toCell.setCellValue(fromCell.getBooleanCellValue());
                            // System.out.println("--------TYPE_BOOLEAN:" +
                            // targetCell.getBooleanCellValue());
                            break;
                        case ERROR:
                            toCell.setCellErrorValue(fromCell.getErrorCellValue());
                            // System.out.println("--------TYPE_ERROR:" +
                            // targetCell.getErrorCellValue());
                            break;
                        case FORMULA:
                            toCell.setCellFormula(parseFormula(fromCell.getCellFormula()));
                            // System.out.println("--------TYPE_FORMULA:" +
                            // targetCell.getCellFormula());
                            break;
                        case NUMERIC:
                            toCell.setCellValue(fromCell.getNumericCellValue());
                            // System.out.println("--------TYPE_NUMERIC:" +
                            // targetCell.getNumericCellValue());
                            break;
                        case STRING:
                            toCell.setCellValue(fromCell.getRichStringCellValue());
                            // System.out.println("--------TYPE_STRING:" + i +
                            // targetCell.getRichStringCellValue());
                            break;
                        default:
                            break;
                    }
                }
            }
        }
    }

    /**
     * 复制单元格样式
     * 此方法主要用于复制2个不同Workbook间的单元格样式
     */
    public static void copyCellStyle(Workbook fromWorkbook, Workbook toWorkbook, CellStyle fromStyle, CellStyle toStyle) {
        toStyle.setAlignment(fromStyle.getAlignment());

        // 边框和边框颜色
        toStyle.setBorderBottom(fromStyle.getBorderBottom());
        toStyle.setBorderLeft(fromStyle.getBorderLeft());
        toStyle.setBorderRight(fromStyle.getBorderRight());
        toStyle.setBorderTop(fromStyle.getBorderTop());
        toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
        toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
        toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
        toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());

        // 字体
        Font tofont = toWorkbook.createFont();
        copyFont(fromWorkbook.getFontAt(fromStyle.getFontIndexAsInt()), tofont);
        toStyle.setFont(tofont);

        // 背景和前景
        toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
        toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());

        toStyle.setDataFormat(fromStyle.getDataFormat());
        toStyle.setFillPattern(fromStyle.getFillPattern());
        toStyle.setHidden(fromStyle.getHidden());
        toStyle.setIndention(fromStyle.getIndention());
        toStyle.setLocked(fromStyle.getLocked());
        toStyle.setRotation(fromStyle.getRotation());
        toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
        toStyle.setWrapText(fromStyle.getWrapText());
    }

    /**
     * 复制字体
     * 此方法主要用于复制2个不同Workbook间的字体
     */
    public static void copyFont(Font fromFont, Font toFont) {
        toFont.setBold(fromFont.getBold());
        toFont.setCharSet(fromFont.getCharSet());
        toFont.setColor(fromFont.getColor());
        toFont.setFontHeight(fromFont.getFontHeight());
        toFont.setFontHeightInPoints(fromFont.getFontHeightInPoints());
        toFont.setFontName(fromFont.getFontName());
        toFont.setItalic(fromFont.getItalic());
        toFont.setStrikeout(fromFont.getStrikeout());
        toFont.setTypeOffset(fromFont.getTypeOffset());
        toFont.setUnderline(fromFont.getUnderline());
    }

    public static void copyPicture(Workbook fromWorkbook, Workbook toWorkbook) throws IOException {
        if (fromWorkbook instanceof HSSFWorkbook) {
            copyHSSFPicture((HSSFWorkbook) fromWorkbook, (HSSFWorkbook) toWorkbook);
        } else if (fromWorkbook instanceof XSSFWorkbook) {
            copyXSSFPicture((XSSFWorkbook) fromWorkbook, (XSSFWorkbook) toWorkbook);
        }
    }

    public static void copyHSSFPicture(HSSFWorkbook fromWorkbook, HSSFWorkbook toWorkbook) {
        int pictureIndex = 0;
        for(int i =0 ;i < fromWorkbook.getNumberOfSheets() ; i++){
            HSSFPatriarch patriarch = fromWorkbook.getSheetAt(i).getDrawingPatriarch();
            HSSFPatriarch patriarch2 = toWorkbook.getSheetAt(i).createDrawingPatriarch();
            List<HSSFShape> list = patriarch.getChildren();
            for (HSSFShape shape : list) {
                if (shape instanceof HSSFPicture) {
                    HSSFPicture picture = (HSSFPicture) shape;
                    HSSFClientAnchor anchor = (HSSFClientAnchor) picture.getAnchor();
                    patriarch2.createPicture(anchor, toWorkbook.addPicture(picture.getPictureData().getData(), HSSFWorkbook.PICTURE_TYPE_PNG));
                }
            }

        }
    }

    public static void copyXSSFPicture(XSSFWorkbook fromWorkbook, XSSFWorkbook toWorkbook) {
        int pictureIndex = 0;
        for(int i =0 ;i < fromWorkbook.getNumberOfSheets() ; i++){
            List<POIXMLDocumentPart> list = fromWorkbook.getSheetAt(i).getRelations();
            for (POIXMLDocumentPart part : list) {
                if (part instanceof XSSFDrawing) {
                    XSSFDrawing drawing = (XSSFDrawing) part;
                    XSSFDrawing drawing2 = toWorkbook.getSheetAt(i).createDrawingPatriarch();
                    List<XSSFShape> shapes = drawing.getShapes();
                    for (XSSFShape shape : shapes) {
                        if (shape instanceof XSSFPicture) {
                            XSSFPicture picture = (XSSFPicture) shape;
                            XSSFClientAnchor anchor = picture.getClientAnchor();
                            toWorkbook.addPicture(picture.getPictureData().getData(), XSSFWorkbook.PICTURE_TYPE_PNG);
                            drawing2.createPicture(anchor, pictureIndex++);
                        }
                    }
                }
            }
        }
    }

    private static String parseFormula(String pPOIFormula) {
        final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
        StringBuilder result = new StringBuilder();
        int index;

        index = pPOIFormula.indexOf(cstReplaceString);
        if (index >= 0) {
            result.append(pPOIFormula.substring(0, index));
            result.append(pPOIFormula.substring(index + cstReplaceString.length()));
        } else {
            result.append(pPOIFormula);
        }

        return result.toString();
    }

    /**
     * 根据单元格信息动态插入图片，如果单元格有文字，图片的位置会在文字之后，如果同样的位置已有图片则会往下插入
     *
     * @param workbook    Excel
     * @param cell        单元格信息
     * @param inputStream 图片输入流
     * @param scale       图片缩放，传入null表示原始尺寸，其余表示图片高于行高的比（例如传入1.5，表示该图片占1.5个行高）
     */
    public static void createPicture(Workbook workbook, Cell cell, InputStream inputStream, Double scale) {
        ByteArrayOutputStream byteArrayOut = null;
        try {
            byteArrayOut = new ByteArrayOutputStream();
            BufferedImage bufferImg = ImageIO.read(inputStream);
            ImageIO.write(bufferImg, "png", byteArrayOut);

            if (cell != null && (cell.getCellType() == CellType.STRING || cell.getCellType() == CellType.BLANK)) {
                Sheet sheet = cell.getSheet();
                Row row = cell.getRow();
                String cellValue = cell.getStringCellValue().contains("#{") ? cell.getStringCellValue().split("#\\{")[0] : cell.getStringCellValue();

                int i = row.getRowNum();
                short j = (short) cell.getColumnIndex();

                // 单元格像素宽度
                int colWidth = sheet.getColumnWidth(cell.getColumnIndex()) / 32;
                // 单元格文本大致像素宽度
                int wordWidth = cellValue.getBytes("GBK").length == 0 ? 0 : ((cellValue.getBytes("GBK").length + 2) * 8);
                double pert = new BigDecimal(wordWidth).divide(new BigDecimal(colWidth), 10, BigDecimal.ROUND_HALF_UP).doubleValue();

                int dx1 = new BigDecimal(pert * 1023).setScale(0, BigDecimal.ROUND_HALF_UP).intValue();
                int dy1 = 0;

                if (sheet instanceof HSSFSheet) {
                    List<HSSFShape> shapes = ((HSSFSheet) sheet).getDrawingPatriarch().getChildren();
                    for (HSSFShape shape : shapes) {
                        HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                        if (anchor.getRow1() == i && anchor.getCol1() == j && anchor.getDx1() == dx1 && anchor.getDy1() == dy1) {
                            if (anchor.getDy2() >= 255) {
                                i = anchor.getRow2() + 1;
                                dy1 = 0;
                            } else {
                                i = anchor.getRow2();
                                dy1 = anchor.getDy2() + 1;
                            }
                        }
                    }
                } else if (sheet instanceof XSSFSheet) {
                    List<XSSFShape> shapes = ((XSSFSheet) sheet).getDrawingPatriarch().getShapes();
                    for (XSSFShape shape : shapes) {
                        XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
                        if (anchor.getRow1() == i && anchor.getCol1() == j && anchor.getDx1() == dx1 && anchor.getDy1() == dy1) {
                            if (anchor.getDy2() >= 255) {
                                i = anchor.getRow2() + 1;
                                dy1 = 0;
                            } else {
                                i = anchor.getRow2();
                                dy1 = anchor.getDy2() + 1;
                            }
                        }
                    }
                }

                // 由于用了getPreferredSize所以dx2,dy2无效
                HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, 0, 0, j, i, j, i + 1);
                anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
                if (sheet instanceof HSSFSheet) {
                    HSSFPatriarch patriarch = ((HSSFSheet) sheet).createDrawingPatriarch();
                    if (scale == null) {
                        patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)).getPreferredSize(1.0);
                    } else {
                        double zoom = new BigDecimal(row.getHeight() / 15).divide(new BigDecimal(bufferImg.getHeight()), 10, BigDecimal.ROUND_HALF_UP).doubleValue(); // 行高像素与图片高度像素比例
                        patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)).getPreferredSize(zoom * scale);
                    }
                } else if (sheet instanceof XSSFSheet) {
                    XSSFDrawing drawing = ((XSSFSheet) sheet).createDrawingPatriarch();
                    if (scale == null) {
                        drawing.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)).getPreferredSize(1.0);
                    } else {
                        double zoom = new BigDecimal(row.getHeight() / 15).divide(new BigDecimal(bufferImg.getHeight()), 10, BigDecimal.ROUND_HALF_UP).doubleValue(); // 行高像素与图片高度像素比例
                        drawing.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)).getPreferredSize(zoom * scale);
                    }
                }


            }
        } catch (IOException e) {
            e.printStackTrace();
            throw new ExcelException("插入图片失败");
        } finally {
            if (byteArrayOut != null) {
                try {
                    byteArrayOut.close();
                } catch (IOException e) {
                    e.printStackTrace();
                    throw new ExcelException("关闭ByteArrayOutputStream失败");
                }
            }
        }
    }

    public static ByteArrayOutputStream parse(InputStream in) throws IOException {
        ByteArrayOutputStream swapStream = new ByteArrayOutputStream();
        int ch;
        while ((ch = in.read()) != -1) {
            swapStream.write(ch);
        }
        return swapStream;
    }

    //outputStream转inputStream
    public static ByteArrayInputStream parse(OutputStream out) {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        baos = (ByteArrayOutputStream) out;
        ByteArrayInputStream swapStream = new ByteArrayInputStream(baos.toByteArray());
        return swapStream;
    }

    public static void setValue(Row row, int column, String value, CellStyle cellStyle) {
        row.createCell(column).setCellValue(value);
        row.getCell(column).setCellStyle(cellStyle);
    }

    public static void setValue(Row row, int column, Integer value, CellStyle cellStyle) {
        row.createCell(column).setCellValue(value);
        row.getCell(column).setCellStyle(cellStyle);
    }

    public static void setValue(Row row, int column, Double value, HSSFCellStyle cellStyle) {
        row.createCell(column).setCellValue(value);
        row.getCell(column).setCellStyle(cellStyle);
    }

    /**
     * 取消合并单元格
     *
     * @param sheet
     * @param rowIndex
     */
    public static void removeMergedRegion(XSSFSheet sheet, int rowIndex) {
        int mergedCount = sheet.getNumMergedRegions();
        for (int i = mergedCount - 1; i >= 0; i--) {
            CellRangeAddress temp = sheet.getMergedRegion(i);
            if (temp.getFirstRow() == rowIndex) {
                sheet.removeMergedRegion(i);
            }
        }
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    private static  boolean isMergedRegion(Sheet sheet, int row , int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRegionValue(Sheet sheet ,int row , int column){
        int sheetMergeCount = sheet.getNumMergedRegions();

        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getStringCellValue(fCell) ;
                }
            }
        }

        return null ;
    }

}
